
// A2_Merge_years
//==============================================================================

// Description: This file crosswalks the data to time-consistent pc8plus categories

clear
set more off
cd "D:\data_replication"


// Import pc8 - pc8plus Crosswalk
//------------------------------------------------------------------------------

use data\crosswalks\pc8_over_time\pc8_pc8plus_2003_2007.dta, clear
rename pc8 prccode
tostring prccode, replace
tostring pc8plus, replace
save estimation\1_data_format\crosswalk_pc8_pc8plus_2003_2007_temp.dta, replace
save data\crosswalks\pc8_over_time\crosswalk_pc8_pc8plus_2003_2007_temp.dta, replace


// Import Crosswalk for manually matched PC8
//------------------------------------------------------------------------------

clear
import delimited data\crosswalks\pc8_over_time\manually_matched_pc8_over_time.csv
keep if consistent == 1
gen pc8plus = prccode
expand 5
sort prccode
by prccode: gen id = _n
gen year = 2003 if id == 1
replace year = 2004 if id == 2
replace year = 2005 if id == 3
replace year = 2006 if id == 4
replace year = 2007 if id == 5
keep prccode pc8plus year
save estimation\1_data_format\manually_matched_pc8_over_time.dta, replace
save data\crosswalks\pc8_over_time\manually_matched_pc8_over_time.dta, replace

clear
import delimited data\crosswalks\pc8_over_time\manually_matched_pc8_over_time_2.csv
save estimation\1_data_format\manually_matched_pc8_over_time_2.dta, replace
save data\crosswalks\pc8_over_time\manually_matched_pc8_over_time_2.dta, replace


// Import Data
//------------------------------------------------------------------------------

use estimation\1_data_format\data_summary_1_2003.dta, clear
append using estimation\1_data_format\data_summary_2_2003.dta
append using estimation\1_data_format\data_summary_3_2003.dta
append using estimation\1_data_format\data_summary_4_2003.dta
append using estimation\1_data_format\data_summary_1_2004.dta
append using estimation\1_data_format\data_summary_2_2004.dta
append using estimation\1_data_format\data_summary_3_2004.dta
append using estimation\1_data_format\data_summary_4_2004.dta
append using estimation\1_data_format\data_summary_1_2005.dta
append using estimation\1_data_format\data_summary_2_2005.dta
append using estimation\1_data_format\data_summary_3_2005.dta
append using estimation\1_data_format\data_summary_4_2005.dta
append using estimation\1_data_format\data_summary_1_2006.dta
append using estimation\1_data_format\data_summary_2_2006.dta
append using estimation\1_data_format\data_summary_3_2006.dta
append using estimation\1_data_format\data_summary_4_2006.dta
append using estimation\1_data_format\data_summary_1_2007.dta
append using estimation\1_data_format\data_summary_2_2007.dta
append using estimation\1_data_format\data_summary_3_2007.dta
append using estimation\1_data_format\data_summary_4_2007.dta


// Match PC8 to PC8plus using BBV
//------------------------------------------------------------------------------

merge m:1 prccode year using estimation\1_data_format\crosswalk_pc8_pc8plus_2003_2007_temp.dta
gen match = 3
replace match = 1 if _merge == 1
drop if _merge == 2    															// products in crosswalk but not in dataset
drop _merge
gen pc8plus_temp = pc8plus
drop pc8plus


// Match remaining PC8 via Manual Match 1
//------------------------------------------------------------------------------

merge m:1 prccode year using estimation\1_data_format\manually_matched_pc8_over_time.dta
drop if _merge == 2
tab prccode if _merge == 1 & match == 1
replace pc8plus_temp = pc8plus if _merge == 3
drop _merge pc8plus


// Match remaining PC8 via Manual Match 2
//------------------------------------------------------------------------------

merge m:1 prccode year using estimation\1_data_format\manually_matched_pc8_over_time_2.dta
replace pc8plus_temp = pc8plus if _merge == 3
drop exit synthetic match _merge pc8plus
rename pc8plus_temp pc8plus


// Delete observations with missing prices
//------------------------------------------------------------------------------

gen index_production = 0
replace index_production = 1 if declarant == partner
sort prccode
by prccode: egen price_prod_temp = mean(price_pc8) if index_production == 1
by prccode: egen price_trade_temp = mean(price_pc8) if index_production == 0
by prccode: egen price_prod = mean(price_prod_temp)
by prccode: egen price_trade = mean(price_trade_temp)
drop price_prod_temp price_trade_temp
gen ratio_prod_trade = price_prod / price_trade
drop if ratio_prod_trade == .  													// deletes if no trade or production price for a product
keep prccode pc8plus year quarter declarant partner imports_value_pc8 imports_quantity_pc8 price_pc8 unit_trade

drop if price_pc8 == .


// Drop Cases of multiple units per pc8plus
//------------------------------------------------------------------------------

sort pc8plus unit_trade
by pc8plus unit_trade: gen count_unit = _N
sort pc8plus
by pc8plus: gen count_product = _N
drop if count_unit != count_product
drop count_unit count_product


// Create Variables by pc8plus, format, and save
//------------------------------------------------------------------------------

sort pc8plus year quarter declarant partner
by pc8plus year quarter declarant partner: egen imports_quantity_pc8plus = sum(imports_quantity_pc8)
by pc8plus year quarter declarant partner: egen imports_value_pc8plus = sum(imports_value_pc8)
by pc8plus year quarter declarant partner: keep if _n == 1
gen price_pc8plus = imports_value_pc8plus / imports_quantity_pc8plus

keep pc8plus year quarter declarant partner imports_value_pc8plus imports_quantity_pc8plus price_pc8plus
sort pc8plus year quarter declarant partner
order pc8plus year quarter declarant partner imports_value_pc8plus imports_quantity_pc8plus price_pc8plus

save estimation\1_data_format\production_imports.dta, replace


